<?php

namespace app\common\command;

use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\Db;

class BookReport extends Command
{
    protected function configure()
    {
        $this->setName('book_stats')
            ->addArgument('action', Argument::OPTIONAL, "方法名称")
            ->addOption('start_date', null, Option::VALUE_REQUIRED, '开始日期')
            ->addOption('end_date', null, Option::VALUE_REQUIRED, '结束日期')
            ->setDescription('书籍数据汇总 -- 已废弃');
    }

    protected function execute(Input $input, Output $output)
    {

        $action = $input->getArgument('action');

        if (method_exists($this, $action)) {

            $start_date = $input->hasOption('start_date') ? $input->getOption('start_date') : date('Y-m-d', strtotime('-1days'));
            $end_date = $input->hasOption('end_date') ? $input->getOption('end_date') : date('Y-m-d', strtotime('-1days'));

            $this->$action($start_date, $end_date);
        }

    }

    /*书籍汇总报表 -- 投放号维度*/
    public function daily_channel($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        foreach ($date_rang as $date) {

            $list = Db::table('pay_order_success')
                ->field('channel_id,nid,link_id,SUM( total_fee ) AS total_fee ')
                ->where('status',2)
                ->where('create_date',$date)->group('channel_id,nid')
                ->select();

            if($list) {
                foreach ($list as $lv) {

                    $new_user_num = Db::table('quick_user')
                        ->where('source_novel_id',$lv['nid'])
                        ->where('source_channel_id',$lv['channel_id'])
                        ->where('regdate',$date)
                        ->count('id');

                    $accumulated_recharge_money = $lv['total_fee'];

                    $cost = Db::table('rp_cost')->where('channel_id',$lv['channel_id'])
                        ->where('cost_date',$date)
                        ->sum('money');

                    if($cost < 1)
                    {
                        $cost = Db::table('rp_cost_manual')->where('channel_id',$lv['channel_id'])
                            ->where('link_id',$lv['link_id'])
                            ->where('cost_date',$date)
                            ->sum('money');
                    }


                    $profit = $accumulated_recharge_money > 0 ? $accumulated_recharge_money - $cost : 0;

                    $record = Db::table('rp_book_daily_channel')
                        ->where(['channel_id'=>$lv['channel_id'],'report_date'=>$date,'nid'=>$lv['nid']])->field('id')->find();

                    if($record){

                        Db::table('rp_book_daily_channel')->where(['id'=>$record['id']])->update([
                            'new_user_num'                  => $new_user_num,
                            'accumulated_recharge_money'    => $accumulated_recharge_money,
                            'cost'                          => $cost,
                            'profit'                        => $profit,
                        ]);

                    }else{

                        Db::table('rp_book_daily_channel')->insert([
                            'new_user_num'                  => $new_user_num,
                            'accumulated_recharge_money'    => $accumulated_recharge_money,
                            'cost'                          => $cost,
                            'profit'                        => $profit,
                            'channel_id'                    => $lv['channel_id'],
                            'nid'                           => $lv['nid'],
                            'report_date'                   => $date
                        ]);
                    }

                }
            }

        }

        $end_date_t = date('Y-m-d');
        $start_date_t = date('Y-m-d',strtotime('-30days'));
        $list_t = Db::table('rp_book_daily_channel')
            ->where('report_date','>=',$start_date_t)
            ->where('report_date','<=',$end_date_t)
            ->field('id,report_date,channel_id,nid,accumulated_recharge_money,cost')
            ->select();

        foreach ($list_t as $lv)
        {
            $this->updateRateRecovery($lv,$lv['report_date'],'rp_book_daily_channel');
        }

    }

    /*书籍汇总报表 -- 代理维度*/
    public function daily_agent($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        $roleId = Db::table('cms_manager_role')->where('alias','agent')->value('id');
        $agents = Db::table('cms_manager_user')->where('role_id',$roleId)->column('id');

        foreach ($agents as $agent_id)
        {
            $channelIds = Db::table('cms_manager_user')->where('parent_id',$agent_id)->column('id');
            if(empty($channelIds))
            {
                continue;
            }

            $channelIdStr = implode(',',$channelIds);

            foreach ($date_rang as $v) {

                $sql = "SELECT 
                    report_date,nid,SUM(new_user_num) as new_user_num,SUM(accumulated_recharge_money) as accumulated_recharge_money,
                    SUM(cost) as cost,SUM(profit) as profit                
                FROM `rp_book_daily_channel` 
                WHERE report_date = '{$v}' AND channel_id in ({$channelIdStr})
                GROUP by report_date,nid";

                $list = Db::query($sql);

                if($list){

                    foreach ($list as $lv)
                    {
                        $record = Db::table('rp_book_daily_agent')
                            ->where(['report_date'=>$v,'agent_id'=>$agent_id,'nid'=>$lv['nid']])
                            ->field('id')->find();

                        if($record){

                            Db::table('rp_book_daily_agent')->where(['id'=>$record['id']])->update([
                                'new_user_num'                  => $lv['new_user_num'],
                                'accumulated_recharge_money'    => $lv['accumulated_recharge_money'],
                                'cost'                          => $lv['cost'],
                                'profit'                        => $lv['profit'],
                            ]);

                        }else{

                            Db::table('rp_book_daily_agent')->insert([
                                'new_user_num'                  => $lv['new_user_num'],
                                'accumulated_recharge_money'    => $lv['accumulated_recharge_money'],
                                'cost'                          => $lv['cost'],
                                'profit'                        => $lv['profit'],
                                'nid'                           => $lv['nid'],
                                'report_date'                   => $v,
                                'agent_id'                      => $agent_id
                            ]);
                        }
                    }


                }
            }
        }

        $end_date_t = date('Y-m-d');
        $start_date_t = date('Y-m-d',strtotime('-30days'));
        $list_t = Db::table('rp_book_daily_agent')
            ->where('report_date','>=',$start_date_t)
            ->where('report_date','<=',$end_date_t)
            ->field('id,report_date,agent_id,nid,accumulated_recharge_money,cost')
            ->select();

        foreach ($list_t as $lv)
        {
            $this->updateRateRecovery($lv,$lv['report_date'],'rp_book_daily_agent');
        }
    }

    /*书籍汇总报表 -- 运营或超管维度*/
    public function daily($start_date,$end_date)
    {
        if ($start_date == $end_date) {
            $start_date = date('Y-m-d', strtotime('-1days'));
            $end_date = date('Y-m-d', strtotime('-1days'));
        }

        $date_rang = getDateByRange($start_date, $end_date);

        //每日数据汇总
        $sql = "SELECT 
                   report_date,nid,SUM(new_user_num) as new_user_num,SUM(accumulated_recharge_money) as accumulated_recharge_money,
                    SUM(cost) as cost,SUM(profit) as profit       
                FROM `rp_book_daily_channel` 
                WHERE report_date = ?
                GROUP by report_date,nid";

        foreach ($date_rang as $v) {

            $list   = Db::query($sql,[$v]);

            if($list){

                foreach ($list as $lv){

                    $record = Db::table('rp_book_daily')->where(['report_date'=>$v,'nid'=>$lv['nid']])->field('id')->find();

                    if($record){

                        Db::table('rp_book_daily')->where(['id'=>$record['id']])->update([
                            'new_user_num'                  => $lv['new_user_num'],
                            'accumulated_recharge_money'    => $lv['accumulated_recharge_money'],
                            'cost'                          => $lv['cost'],
                            'profit'                        => $lv['profit'],
                        ]);

                    }else{

                        Db::table('rp_book_daily')->insert([
                            'new_user_num'                  => $lv['new_user_num'],
                            'accumulated_recharge_money'    => $lv['accumulated_recharge_money'],
                            'cost'                          => $lv['cost'],
                            'profit'                        => $lv['profit'],
                            'nid'                           => $lv['nid'],
                            'report_date'                   => $v
                        ]);
                    }

                }

            }
        }

        $end_date_t = date('Y-m-d');
        $start_date_t = date('Y-m-d',strtotime('-30days'));
        $list_t = Db::table('rp_book_daily')
            ->where('report_date','>=',$start_date_t)
            ->where('report_date','<=',$end_date_t)
            ->field('id,report_date,nid,accumulated_recharge_money,cost')
            ->select();

        foreach ($list_t as $lv)
        {
            $this->updateRateRecovery($lv,$lv['report_date'],'rp_book_daily');
        }
    }

    /*更新指定日期回收率*/
    private function updateRateRecovery($lv,$report_date,$table)
    {
        $report_date = date('Y-m-d', strtotime('+1 day', strtotime($report_date)));
        $end_date = date('Y-m-d', strtotime('+4 day', strtotime($report_date)));

        $date_rang = getDateByRange($report_date, $end_date);
        $data = $this->getT6RangeRateRecovery($lv,$date_rang,$table);
     
        if($table == 'rp_book_daily_channel')
        {
            Db::table('rp_book_daily_channel')->where('id',$lv['id'])->update($data);
        }

        if($table == 'rp_book_daily_agent')
        {
            Db::table('rp_book_daily_agent')->where('id',$lv['id'])->update($data);
        }
        if($table == 'rp_book_daily')
        {
            Db::table('rp_book_daily')->where('id',$lv['id'])->update($data);
        }
    }

    /*获取t2-t6回收率*/
    private function getT6RangeRateRecovery($lv,$date_rang,$table)
    {
        $data = [];
        $i = 2;
        $rate_total = 0;
        foreach ($date_rang as $date)
        {

            if($table == 'rp_book_daily_channel')
            {
                $record = Db::table('rp_book_daily_channel')->where('channel_id',$lv['channel_id'])
                    ->where('nid',$lv['nid'])->where('report_date',$date)->field('accumulated_recharge_money,cost')->findOrEmpty();
            }
            if($table == 'rp_book_daily_agent')
            {
                $record = Db::table('rp_book_daily_agent')->where('agent_id',$lv['agent_id'])
                    ->where('nid',$lv['nid'])->where('report_date',$date)->field('accumulated_recharge_money,cost')->findOrEmpty();
            }
            if($table == 'rp_book_daily')
            {
                $record = Db::table('rp_book_daily_agent')
                    ->where('nid',$lv['nid'])->where('report_date',$date)->field('accumulated_recharge_money,cost')->findOrEmpty();
            }

            $rate = $record ? getQuotient($record['accumulated_recharge_money'],$record['cost'],2,'m',100) : 0;

            $data['t'.$i] = $rate+$rate_total;

            $rate_total = $rate+$rate_total;
            $i++;

        }

        return $data;
    }


}